SQLite commands and general usage
参考:https://www.pantz.org/software/sqlite/sqlite_commands_and_general_usage.html
SQLite is an embedded open source relational database (db). It is very portable, easy to use, compact, efficient, and reliable. Being an embedded database it becomes part of the program that hosts it. It is embedded in many popular programs used today. Most use the SQLite C API to interface with the SQLite db. Many scripting/programming languages use the API like the Perl module DBI::SQLite, PHP's data objects with the SQLite driver, or just straight C programs. Not only can these languages (and many more) use the SQLite C API to access the SQLite db but most OS's have a statically linked native binary that can also be controlled completely from the command line. How cool is that?
Installing
SQLite can be installed by going to the SQLite website and downloading a copy and installing it. They have binaries for most major platforms (Windows, MAC, or Linux). If your using a OS with a package manager then there is a good chance you can just install it by using your package manager.
There are 2 versions of SQLite. Version 2 and version 3. Version 2 is (as you may have guessed) is the older version. SQLite 3 databases are not compatible with SQLite 2 databases. Use SQLite 3 if at all possible. If your going to install the SQLite command line version then here is something to remember. The package for SQLite 2 is just called "SQLite". The package for SQLite version 3 is called "SQLite3". These are also the names for the command line binaries. You access a version 2 db with the "SQLite" binary (program). You access a version 3 db with the "SQLite3" binary.
Here are some examples of installing SQLite version 3 on some Linux and BSD's.
On Linux distros with the apt-get package manager (Ex: Debian or Ubuntu).
sudo apt-get install SQLite3
On Linux distros with the yum package manager (Ex: RedHat, CentOS, or Fedora).
yum install SQLite3
On OpenBSD 4.4.
pkg_add -v ftp://ftp.openbsd.org/pub/OpenBSD/4.4/packages/i386/SQLite3-3.5.9p0.tgz
You get the idea. Just install the SQLite3 package for whatever OS you have. If your OS does not have a package just go to the SQLite website then download and install their binary.
SQLite command line
The following commands will be using the statically linked command-line program. This is what was installed in the install section above. You can completely control an SQLite database with this program and it's special built in shell. Some commands in the following examples will be invoked from a Linux shell and and from the SQLite shell. The SQLite shell will consider any command with . (period) in front of it a SQLite command. You can get a complete list of valid SQLite commands by typing .help at the SQLite shell. The commands that don't begin with a period are just regular SQL commands. They should work with most databases.
The examples below are done from a Linux machine with version 3.5.9 of SQLite.
Below when you see user@host:~$ it means execute the command from the shell. When you see sqlite> it means from a SQLite prompt (shell) after selecting a db.
Making/accessing a SQLite database.
This command will not make the db (the file test.db) until you create something in it (Ex: table or view) or until you run a few select commands from the SQLite prompt (like .database). This is so you can change things like page size or character encoding before the database structure is written to the file. After executing the command you will see a SQLite prompt. Make a table or a view to have the file actually written to disk.
If you already have a database file called test.db then you can select and access it with the same command. In summary if the db file does not exist this command will create it and if it does exist then it will select it to be accessed.
user@host:~$ sqlite3 test.db
sqlite> .open "test.db"
sqlite> SELECT * FROM table_name ... ;
Make your output pretty
Before we start creating and looking at data we are going to change a few default settings to make the ouput of the commands look better. We will turn on column mode (.mode col). There are many other modes other than col. See .help for more output modes. We will also turn on column headers (.headers on). Both stay on until you exit the SQLite shell or change them to something else.
sqlite> .mode col
sqlite> .headers on
Creating tables
Let's make a table called test. After the table is made the database file will exist on disk. The table named test and have 2 columns. One called ids and one called value. The ids column is an integer primary key, SQLite will make this an auto increment column. Which means if there is no number then SQLite will generate a number for the column on the first insert. After the first insert it will auto increment the numbers from there. All of this is done with the SQL create command.
sqlite> create table test (ids integer primary key, value text);
Creating views
Let's create a view. Views are like virtual tables. People also call them derived tables. This is because their contents are derived from other tables. Views look like regular tables, but they are not. Views are dynamically generated. A view is made up of relational expressions and takes other tables to produce a new table.
The following example will create a view called testview and will select everything from the test table we just created. It's a silly example of what views are good for but it will work. Most views are good for queries you keep using over and over again. You create the view to use as a temporary table and then query the view just like a table. The view eliminates the need to keep using that same redundant query.
Creating views like creating tables is done with the SQL create command.
sqlite> create view testview AS select * from test;
Creating indexes
Indexes designed to speed up queries under certain conditions. If our test table had 100,000 entries and we wanted to find a small group of entries the database would have to scan all 100,000 entries looking for our small group. If we make an index of all these entries then we will be able to scan all the entries much faster.
The bad thing is that indexes increase the size of the database because they keep a copy of all the columns they index. One other bad thing is that if you insert, update or delete a record the database has to modify the record and the index of that table. This means that indexes can slow down inserts, updates, deletes, etc. If used wisely indexes can give you a huge performance boost. You will just have to test them for yourself. Here is how to make an index for our table test on column value.
Creating indexes like creating tables and views is done with the SQL create command.
sqlite> create index testindex on test (value);
Listing views, tables, and indexes
To see all the tables and views in the database we use the SQLite .tables command. You can also use the SQL like operator to find tables and views with a pattern. Using the % symbol in the example below we show all the tables and views beginning with the letter t.
sqlite> .tables
test testview
sqlite> .tables t%
test
To view any indexes a table has we can use the SQLite .indices command. To see them for our test table we would do the following.
sqlite> .indices test
testindex
Listing schema information
To see database schema information we use the SQLite .schema command. This give you the create command that was used to create the tables. If you created an index on any columns then this will also show up.
sqlite> .schema test
CREATE TABLE test (ids integer primary key, value text);
CREATE INDEX testindex on test (value);
Inserting data into tables
Now let's put in some data in the db with some SQL insert statements.
sqlite> insert into test (value) values('value1');
sqlite> insert into test (value) values('value2');
sqlite> insert into test (value) values('value3');
sqlite> insert into test (value) values('value4');
Seeing the data
Let's use the SQL select command to look at the data we inserted. Remember we turned on column mode (.mode col) and column headers (.headers on) at the beginning of this page so it's easier to read the output.
sqlite> select * from test;
ids value
---------- ----------
1 value1
2 value2
3 value3
4 value4
Seeing your SQLite databases
To see the databases that are currently open use the SQLite .databases command. It will show the main and temp databases and where they are on the system.
sqlite> .databases
seq name file
--- --------------- ------------------------------------
0 main /home/user/test.db
1 temp /var/tmp/etilqs_oCUXLLbZO4TjUNI
Exporting your data
You can export all of your database objects to a file for backup using the SQLite .output and .dump commands. These commands will output all of the data from the database. The first .output line directs any output from now on to the file /tmp/test.sql. The second line .dump will export all of the data in the objects in the database. If the file is already there it will overwrite that file. The last command will set the output back to the screen like we had it.
sqlite> .output /tmp/test.sql
sqlite> .dump
sqlite> .output stdout
To look at what the file looks like open a new shell and cat that file.
user@host:~$ cat /tmp/test.sql
BEGIN TRANSACTION;
CREATE TABLE test (ids integer primary key, value text);
INSERT INTO "test" VALUES(1,'value1');
INSERT INTO "test" VALUES(2,'value2');
INSERT INTO "test" VALUES(3,'value3');
INSERT INTO "test" VALUES(4,'value4');
CREATE VIEW testview AS select * from test;
CREATE INDEX testindex on test (value);
COMMIT;
The file contains all the information you need to re-create your database. From creating the tables to inserting all the data. This is used mostly to backup databases or to export the data from one database type to another. For backing up purposes it is usually easier to just copy the SQLite3 db file to another location.
Importing external data
If you would like to import the data you exported using the SQLite .dump command it's as easy as using the SQLite .read command. In the example below the first command makes a new database from the system command line (shell). The second command imports the data we exported in the previous example. The last command shows all the data in the new database.
user@host:~$ sqlite3 testrecover.db
sqlite> .read /tmp/test.sql
sqlite> select * from test;
ids value
---------- ----------
1 value1
2 value2
3 value3
4 value4
If you want to import data that is in csv (comma separated value) format into a table you can do it using the SQLite .import command. The csv file has to use separators that SQLite understands. To see the current separators you can use the .show command. SQLite uses the pipe | as the default separator.
sqlite> .show
echo: off
explain: off
headers: on
mode: column
nullvalue: ""
output: stdout
separator: "|"
width:
To change the separator type use the .separator command. This will change it for output mode and for .import.
sqlite> .separator ,
sqlite> .show
echo: off
explain: off
headers: on
mode: column
nullvalue: ""
output: stdout
separator: ","
width:
Now that the separator is changed to a comma we can import data from an external csv file. We will import more data into our current table (test) with the .import command. Make sure your csv file sections match up with your tables and that you have the same number of columns. Also, note that when you export a file now it will be exported with a comma as the separator.
Here is what the test.csv file looks like.
5,value5
6,value6
7,value7
Here is the import from the csv file and what the test table looks like after import.
sqlite> .import /tmp/test.csv test
sqlite> select * from test;
ids value
---------- ----------
1 value1
2 value2
3 value3
4 value4
5 value5
6 value6
7 value7
Removing tables, triggers, views, and indexes
To remove any index, table, trigger, or view you have made use the SQL drop command. The syntax is the word drop then what you want to drop and then the name of the thing you want to drop. Below is and example of dropping one of each kind.
drop index indexname
drop table tablename
drop trigger triggername
drop view viewname
Exiting
To exit out of the SQLite prompt use the SQLite .exit or .quit command. Both work the same way.
sqlite> .exit
user@host:~$
SQLite from the shell
Intro
The following commands will be using the statically linked SQLite command-line program. This is what was installed in the install section above. You can completely control an SQLite database with this program right from the Linux shell. No need to invoke the SQLite shell we introduced above. Being able to access SQLite databases from a shell lets you incorporate SQLite into your shell scripts without the need for external libraries.
We will use the same test.db database that we made in the examples above for this demo.
Usage options
The SQLite binary is for version 3 is called SQLite3. You can use this program from the command line just like any other UNIX program. If you want to look at the options the binary has just issue the --help option.
user@host:~$ sqlite3 --help
Usage: SQLite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist.
OPTIONS include:
-init filename read/process named file
-echo print commands before execution
-[no]header turn headers on or off
-bail stop after hitting an error
-interactive force interactive I/O
-batch force batch I/O
-column set output mode to 'column'
-csv set output mode to 'csv'
-html set output mode to HTML
-line set output mode to 'line'
-list set output mode to 'list'
-separator 'x' set output field separator (|)
-nullvalue 'text' set text string for NULL values
-version show SQLite version
You can see syntax you need to use and the different options available.
NOTE THE ABOVE: If the database does not exist it will be created on any command given. Even on commands that fail.
Execute SQL statements
You can execute SQL statements right from the shell. Let's look at the data in our test database test.db. We are going to turn on headers and also turn on column mode so the ouput looks nice.
user@host:~$ sqlite3 -header -column test.db 'select * from test'
ids value
---------- ----------
1 value1
2 value2
3 value3
4 value4
5 value5
6 value6
7 value7
You can execute any SQL statement against a database like this.
Execute SQLite commands
You can execute SQLite commands the same way you execute SQL commands. The example below uses the .schema SQLite command on the test.db file.
user@host:~$ sqlite3 -header -column test.db '.schema'
CREATE TABLE test (ids integer primary key, value text);
CREATE VIEW testview AS select * from test;
CREATE INDEX testindex on test (value);
Export a database
You can export (backup) your database using the command-line program and .dump. It works the same way it does from the SQLite shell. But we are going to want the output to go to a file and not the screen so we will redirect it to a file called dbbackup.
user@host:~$ sqlite3 test.db '.dump' > dbbackup
You could also compress the output before writing it disk by running .dump's ouput through gzip, bzip2, or whatever. The following is an example using bzip. It is highly recommended that you compress your dumps.
user@host:~$ sqlite3 test.db '.dump' | gzip -c > dbbackup.gz
Import a database
If you have used the SQLite .dump command we mentioned earlier to create a file with SQL statements you can input those statements into your database from the command line. This can be done with any SQL statements not just the ones from the .dump command. The following example will use a file called statements.sql and import them into our test.db file.
user@host:~$ sqlite3 test.db < statements.sql
To import that gzip compressed dump (backup) of SQL statements from above.
user@host:~$ gunzip -c dbbackup.gz | SQLite3 test.db
As a speed note. If your importing a lot of insert statements wrap those in a transaction to get a major speedup.
More information
Here are some great links from the SQLite website to help you further.
SQL commands understood by SQLite
SQLite understands most of the standard SQL language. But there is some syntax differences sometimes. There are also some SQL commands that SQLite has added that are unique. These are commands like VACUUM or PRAGMA. Make sure your look at the PRAGMA page as it is used to tweak settings in your SQLite database.
